package cn.zfs.datasource.service.impl;

import cn.zfs.datasource.advice.GlobalException;
import cn.zfs.datasource.entity.model.DataSourceProperties;
import cn.zfs.datasource.entity.model.SQLModelAndProperties;
import cn.zfs.datasource.entity.model.TableProperties;
import cn.zfs.datasource.entity.vo.DataSourceVO;
import cn.zfs.datasource.service.DataSourceSevice;
import cn.zfs.datasource.util.BasePageList;
import cn.zfs.datasource.util.BaseResponse;
import cn.zfs.datasource.util.DataSourceUtil;
import cn.zfs.datasource.util.StringUtil;
import com.mysql.jdbc.Driver;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.BufferedOutputStream;
import java.io.DataOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

@SuppressWarnings("JavaDoc")
@Service
public class DataSourceServiceImpl implements DataSourceSevice {

    @Override
    public BaseResponse createModel(HttpServletRequest request, HttpServletResponse response, DataSourceVO dataSourceVO) throws SQLException, IOException {
        // 从cookie中获取数据库的连接配置
        DataSourceProperties dataSourceProperties = getDataSourceProperties(request);
        // 创建数据库连接
        Connection connection = createConection(dataSourceProperties);
        QueryRunner queryRunner = new QueryRunner();

        // 获取需要操作的表名字列表
        List<String> tableNames = getTableNames(dataSourceVO, dataSourceProperties, connection, queryRunner);
        if (CollectionUtils.isEmpty(tableNames)) {
            return BaseResponse.fail("该{}数据库不存在表", dataSourceProperties.getTableSchema());
        }
        response.reset();
        response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(dataSourceVO.getZipName(), "UTF-8"));
        ServletOutputStream outputStream = response.getOutputStream();
        ZipOutputStream zipOutputStream = new ZipOutputStream(new BufferedOutputStream(outputStream));
        zipOutputStream.setMethod(ZipOutputStream.DEFLATED); //设置压缩方法
        DataOutputStream os = null;
        for (String tableName : tableNames) {
            zipOutputStream.putNextEntry(
                    new ZipEntry(
                            DataSourceUtil.strConnect(
                                    dataSourceVO.isMkdirPackagePath() ? DataSourceUtil.packagePath(dataSourceVO.getPagePath()) : null,
                                    DataSourceUtil.nameFirst2Big(tableName, dataSourceVO.getConnector()),
                                    ".java")));
            os = new DataOutputStream(zipOutputStream);
            // 获取某表的字段名字和属性
            List<TableProperties> tableProperties = getTableProperties(dataSourceProperties, connection, queryRunner, tableName);
            if (CollectionUtils.isEmpty(tableProperties)) {
                return BaseResponse.fail("该{}表字段为空", tableName);
            }

            // 判断是否设置包路径
            if (StringUtils.isNotBlank(dataSourceVO.getPagePath()) && dataSourceVO.isMkdirPackagePath()) {
                setPackagePath(dataSourceVO, os);
            }

            // 获取类型集合,决定import
            List<TableProperties> dataTypes = getTableDataType(connection, queryRunner, tableName);
            if (CollectionUtils.isEmpty(dataTypes)) {
                return BaseResponse.fail("查询该{}表中字段对应的类型为空", tableName);
            }
            // 根据 dataTypes 导包
            importByDataType(os, dataTypes);

            // lombok插件判断
            if (dataSourceVO.isLombok()) {
                setLombok(os);
            }
            // 设置类名
            os.write((DataSourceUtil.strConnect("public class ", DataSourceUtil.nameFirst2Big(tableName, dataSourceVO.getConnector()), " {\n")).getBytes());
            // 设置属性
            setProperties(dataSourceVO, os, tableProperties);

            // 判断getset方法
            setSetGet(dataSourceVO, connection, queryRunner, os, tableName);
        }
        if (os != null) {
            os.flush();
            os.close();
        }
        zipOutputStream.close();
        return BaseResponse.staticSuccess("", "");
    }

    @Override
    public BaseResponse createDao(HttpServletRequest request, HttpServletResponse response, DataSourceVO dataSourceVO) throws SQLException, GlobalException, IOException {
        // 从cookie中获取数据库的连接配置
        DataSourceProperties dataSourceProperties = getDataSourceProperties(request);
        // 创建数据库连接
        Connection connection = createConection(dataSourceProperties);
        QueryRunner queryRunner = new QueryRunner();

        List<String> tableNames = dataSourceVO.getTableNames();
        if (CollectionUtils.isEmpty(tableNames)) {
            throw new GlobalException("表名列表为空");
        }

        response.reset();
        response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(dataSourceVO.getZipName(), "UTF-8"));
        ServletOutputStream outputStream = response.getOutputStream();
        ZipOutputStream zipOutputStream = new ZipOutputStream(new BufferedOutputStream(outputStream));
        zipOutputStream.setMethod(ZipOutputStream.DEFLATED); //设置压缩方法
        DataOutputStream os = null;

        for (String tableName : tableNames) {
            String className = DataSourceUtil.nameFirst2Big(
                    DataSourceUtil.strConnect(tableName, dataSourceVO.getDaoSuffix()),
                    dataSourceVO.getConnector());
            String fileName = DataSourceUtil.strConnect(
                    dataSourceVO.isCreateDaoPackagePath() ? DataSourceUtil.packagePath(dataSourceVO.getDaoPackagePath()) : null,
                    className,
                    ".java");
            zipOutputStream.putNextEntry(new ZipEntry(fileName));
            os = new DataOutputStream(zipOutputStream);
            List<String> columnNames = queryRunner.query(connection,
                    SQLModelAndProperties.getColumnNames(tableName), new ColumnListHandler<String>());
            String cloumnName = StringUtil.list2StringAndAppendStr(columnNames, "`", "`,`", "`");
            DataSourceUtil.write(os, "public", "interface", className, "{");
            DataSourceUtil.write(os, "\t", "final", "static", "String", "TABLE_NAME=\"", tableName, "\";");
            DataSourceUtil.write(os, "\t", "final", "static", "String", "SELECT_COLUMN=\"", cloumnName, "\";");
            DataSourceUtil.write(os, "\t", "final", "static", "String", "INSERT_COLUMN=\"", cloumnName, "\";");
            DataSourceUtil.write(os, "}");
        }
        if (os != null) {
            os.flush();
            os.close();
        }
        zipOutputStream.close();
        return BaseResponse.staticSuccess("", "");
    }

    public BaseResponse createService(HttpServletRequest request, HttpServletResponse response, DataSourceVO dataSourceVO) throws SQLException {
        // 从cookie中获取数据库的连接配置
        DataSourceProperties dataSourceProperties = getDataSourceProperties(request);
        // 创建数据库连接
        Connection connection = createConection(dataSourceProperties);
        QueryRunner queryRunner = new QueryRunner();

        //

        return null;
    }

    /**
     * 设置set get 方法
     *
     * @param dataSourceVO
     * @param connection
     * @param queryRunner
     * @param os
     * @param tableName
     * @throws SQLException
     * @throws IOException
     */
    private void setSetGet(DataSourceVO dataSourceVO, Connection connection, QueryRunner queryRunner, DataOutputStream os, String tableName) throws SQLException, IOException {
        if (dataSourceVO.isGetAndSet()) {
            List<TableProperties> getSet = queryRunner.query(connection,
                    SQLModelAndProperties.getColumnAndType(tableName), new BeanListHandler<TableProperties>(TableProperties.class));
            if (!CollectionUtils.isEmpty(getSet)) {
                for (TableProperties properties : getSet) {
                    os.write((DataSourceUtil.strConnect(
                            "\tpublic ",
                            DataSourceUtil.convertSqlTypeToJavaType(properties.getDataType(), dataSourceVO.isEncapsulation()),
                            " ",
                            DataSourceUtil.getOrSet(properties.getColumnName(), dataSourceVO.getConnector(), true),
                            " {\n")).getBytes());
                    os.write((DataSourceUtil.strConnect("\t\treturn ", properties.getColumnName(), ";\n")).getBytes());
                    os.write(("\t}\n").getBytes());
                    os.write(("\n").getBytes());
                    os.write((DataSourceUtil.strConnect(
                            "\tpublic ",
                            DataSourceUtil.convertSqlTypeToJavaType(properties.getDataType(), dataSourceVO.isEncapsulation()),
                            " ",
                            DataSourceUtil.getOrSet(properties.getColumnName(), dataSourceVO.getConnector(), false),
                            " {\n")).getBytes());
                    os.write((DataSourceUtil.strConnect("\t\treturn ", properties.getColumnName(), ";\n")).getBytes());
                    os.write(("\t}\n").getBytes());
                    os.write(("\n").getBytes());
                }
            }
        }
        os.write("}".getBytes());
    }

    /**
     * 设置属性
     *
     * @param dataSourceVO
     * @param os
     * @param tableProperties
     * @throws IOException
     */
    private void setProperties(DataSourceVO dataSourceVO, DataOutputStream os, List<TableProperties> tableProperties) throws IOException {
        for (TableProperties tableProperty : tableProperties) {
            os.write(DataSourceUtil.getNotes(tableProperty.getColumnComment(), dataSourceVO.getNotesNum()).getBytes());
            os.write((DataSourceUtil.strConnect(
                    "\tprivate ",
                    DataSourceUtil.convertSqlTypeToJavaType(tableProperty.getDataType(), dataSourceVO.isEncapsulation()),
                    " ",
                    DataSourceUtil.nameTwo2Big(tableProperty.getColumnName(), dataSourceVO.getConnector()),
                    ";\n")
            ).getBytes());
        }
        os.write(("\n").getBytes());
    }

    /**
     * 设置lombok插件
     *
     * @param os
     * @throws IOException
     */
    private void setLombok(DataOutputStream os) throws IOException {
        os.write("import lombok.Data;".getBytes());
        os.write("\nimport lombok.ToString;\n".getBytes());
        os.write("\n@Data\n".getBytes());
        os.write("@ToString\n".getBytes());
    }

    /**
     * 根据 dataTypes 导包
     *
     * @param os
     * @param dataTypes
     * @throws IOException
     */
    private void importByDataType(DataOutputStream os, List<TableProperties> dataTypes) throws IOException {
        ArrayList<String> alreadyImported = new ArrayList<>(dataTypes.size());
        for (TableProperties dataType : dataTypes) {
            if (StringUtils.isNotBlank(dataType.getDataType())) {
                String packagePath = DataSourceUtil.convertSqlTypeToImportPackage(dataType.getDataType());
                if (StringUtils.isNotBlank(packagePath) && !alreadyImported.contains(packagePath)) {
                    alreadyImported.add(packagePath);
                    os.write((DataSourceUtil.strConnect(packagePath, "\n")).getBytes());
                }
            }
        }
        os.write("\n".getBytes());
    }

    /**
     * 获取类型集合,决定import
     *
     * @param connection
     * @param queryRunner
     * @param tableName
     * @return
     * @throws SQLException
     */
    private List<TableProperties> getTableDataType(Connection connection, QueryRunner queryRunner, String tableName) throws SQLException {
        return queryRunner.query(connection,
                SQLModelAndProperties.getDataTypes(tableName), new BeanListHandler<TableProperties>(TableProperties.class));
    }

    /**
     * 设置包路径
     *
     * @param dataSourceVO
     * @param os
     * @throws IOException
     */
    private void setPackagePath(DataSourceVO dataSourceVO, DataOutputStream os) throws IOException {
        os.write((DataSourceUtil.strConnect("package ", dataSourceVO.getPagePath(), ";\n")).getBytes());
        os.write("\n".getBytes());
    }

    /**
     * 获取 表的 列和属性
     *
     * @param dataSourceProperties
     * @param connection
     * @param queryRunner
     * @param tableName
     * @return
     * @throws SQLException
     */
    private List<TableProperties> getTableProperties(DataSourceProperties dataSourceProperties, Connection connection, QueryRunner queryRunner, String tableName) throws SQLException {
        return queryRunner.query(connection,
                SQLModelAndProperties.getTablePropertiesSql(tableName, dataSourceProperties.getTableSchema()), new BeanListHandler<TableProperties>(TableProperties.class));
    }

    /**
     * 获取表名列表
     *
     * @param dataSourceVO
     * @param dataSourceProperties
     * @param connection
     * @param queryRunner
     * @return
     * @throws SQLException
     */
    private List<String> getTableNames(DataSourceVO dataSourceVO, DataSourceProperties dataSourceProperties, Connection connection, QueryRunner queryRunner) throws SQLException {
        List<String> tableNames = null;
        if (dataSourceVO.isSpecifiedTable()) {
            tableNames = dataSourceVO.getTableNames();
        } else {
            // 获取数据库所有的表名字
            tableNames = queryRunner.query(connection,
                    SQLModelAndProperties.getTableNamesSql(dataSourceProperties.getTableSchema()), new ColumnListHandler<String>());
        }
        return tableNames;
    }

    /**
     * 从cookie中获取数据库配置
     *
     * @param request
     * @return
     */
    private DataSourceProperties getDataSourceProperties(HttpServletRequest request) {
        Object dataSourcePropertiesObj = request.getSession().getAttribute("dataSourceProperties");
        DataSourceProperties dataSourceProperties = null;
        if (dataSourcePropertiesObj != null) {
            dataSourceProperties = (DataSourceProperties) dataSourcePropertiesObj;
        }
        return dataSourceProperties;
    }

    @Override
    public BaseResponse getTableNames(HttpServletRequest request, int pageSize, int pageNum, String tableName) throws SQLException {
        BasePageList<Object> pageList = new BasePageList<Object>(pageSize, pageNum);
        DataSourceProperties dataSourceProperties = (DataSourceProperties) request.getSession().getAttribute("dataSourceProperties");
        Connection connection = createConection(dataSourceProperties);
        QueryRunner queryRunner = new QueryRunner();
        Object[] query = queryRunner.query(connection, SQLModelAndProperties.getCount(dataSourceProperties.getTableSchema()), new ArrayHandler());
        List<String> tableNames = queryRunner.query(connection,
                SQLModelAndProperties.getTableNamesSqlToLimit(dataSourceProperties.getTableSchema(), pageList.getPageSize(), pageList.getStart(), tableName), new ColumnListHandler<String>());
        pageList.setData(tableNames);
        pageList.setTotalNum(Integer.valueOf(String.valueOf(query[0])));
        BaseResponse<BasePageList> baseResponse = new BaseResponse<>();
        return baseResponse.success(pageList);
    }

    @Override
    public BaseResponse testConnection(HttpServletRequest request, DataSourceProperties dataSourceProperties) throws SQLException {
        Connection connection = createConection(dataSourceProperties);
        QueryRunner queryRunner = new QueryRunner();
        Object[] query = queryRunner.query(connection, "select 1", new ArrayHandler());
        if (query.length > 0 && Integer.valueOf(String.valueOf(query[0])) == 1) {
            HttpSession session = request.getSession();
            session.setAttribute("dataSourceProperties", dataSourceProperties);
            return BaseResponse.staticSuccess("测试成功");
        }
        return BaseResponse.fail("连接失败", "");
    }

    private Connection createConection(DataSourceProperties dataSourceProperties) throws SQLException {
        DriverManager.registerDriver(new Driver());
        return DriverManager.getConnection(dataSourceProperties.getUrl(), dataSourceProperties.getUsername(), dataSourceProperties.getPassword());
    }

}
